package com.runfast.waimai.web.controller.Runfastbusinesscontroller;

import com.runfast.waimai.dao.model.RunfastGoodsSellChildren;
import com.runfast.waimai.dao.model.RunfastGoodsSellChildrenExample;
import com.runfast.waimai.dao.model.RunfastGoodsSellRecord;
import com.runfast.waimai.service.RunfastGoodsSellChildrenService;
import com.runfast.waimai.utils.ComUtil;
import org.apache.poi.hssf.usermodel.*;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

/**
 * @Created by Ptkc on 2018/7/26 10:42.
 */
public class WriteExcel {

    @Resource
    private RunfastGoodsSellChildrenService runfastGoodsSellChildrenService;

    public HSSFWorkbook countBusinessSales(List<Map<String, Object>> list, List<RunfastGoodsSellRecord> recoedList, Integer type) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        try {

            HSSFSheet sheet = workbook.createSheet();
            HSSFCellStyle setBorder = null;
            //普通格式
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
            cellStyle.setWrapText( true );
            if(sheet!=null){
                HSSFRow row = sheet.createRow(0);
                workbook.setSheetName(0, "商家销售统计");
                String[] columns;
                int[] widths;
                if(type==1){
                    columns=new String[]{"商家编号","代理商","商家名称","订单数量","商品数量","实付金额","商家部分总金额","商家收入（元）","配送费","退款金额","代理商抽成","平台抽成","优惠金额","提成系数","配送方式"};
                    widths=new int[]{100*35,150*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35};
                }else if(type==2){
                    columns=new String[]{"商家编号","代理商","商家名称","订单数量","商品数量","实付金额","商家部分总金额","商家收入（元）","商家补贴（元）","已提现金额","未提现金额","配送费","退款金额","代理商抽成","上级代理商抽成","平台抽成","优惠金额","提成系数","配送方式"};
                    widths=new int[]{100*35,150*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35};
                }else{
                    columns=new String[]{"商家编号","代理商","商家名称","订单数量","商品数量","实付金额","商家部分总金额","商家收入（元）","商家补贴（元）","配送费","退款金额","配送方式"};
                    widths=new int[]{100*35,150*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35};
                }

                HSSFCell cell = null;

                setBorder = workbook.createCellStyle();
                HSSFFont font = workbook.createFont();
                font.setFontName("黑体");
                font.setFontHeightInPoints((short) 12); //设置字体大小
                setBorder.setFont(font);
                setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平布局：居中
                setBorder.setWrapText( true );
                for(int i=0;i<columns.length;i++){
                    sheet.setColumnWidth(i, widths[i]);
                    cell = row.createCell(i);
                    cell.setCellValue(columns[i]);
                    cell.setCellStyle(setBorder);
                }

                int index = 1;
                for(Map<String,Object> objs:list){
                    row = sheet.createRow(index);

                    cell = row.createCell(0);
                    cell.setCellValue(objs.get("id")+"");
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(1);
                    if(objs.get("agentName")!=null){
                        cell.setCellValue((String)objs.get("agentName"));
                    }
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(2);
                    cell.setCellValue((String)objs.get("name"));
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(3);
                    if(objs.get("coun")!=null){
                        cell.setCellValue((Long)objs.get("coun"));
                    }else{
                        cell.setCellValue(0);
                    }
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(4);
                    if(objs.get("shu")!=null){
                        cell.setCellValue(objs.get("shu").toString());
                    }else{
                        cell.setCellValue(0);
                    }
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(5);
                    if(objs.get("totalpay")!=null){
                        cell.setCellValue(objs.get("totalpay").toString());
                    }
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(6);
                    if(objs.get("jin")!=null){
                        /*Double s = (Double) objs.get("jin");
                        cell.setCellValue(s.doubleValue());*/
                        cell.setCellValue(objs.get("jin").toString());
                    }
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(7);
                    if(objs.get("bus")!=null){
                        cell.setCellValue(objs.get("bus").toString());
                    }
                    cell.setCellStyle(cellStyle);

                    if(type==2){
                        cell = row.createCell(8);
                        if(objs.get("subsidy")!=null){
                            cell.setCellValue(objs.get("subsidy").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(9);
                        if(objs.get("yt")!=null){//已提现
                            cell.setCellValue(objs.get("yt").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(10);
                        if(objs.get("wt")!=null){//未提现
                            cell.setCellValue(objs.get("wt").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(11);
                        if(objs.get("showps")!=null){
                            cell.setCellValue(objs.get("showps").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(12);
                        if(objs.get("refund")!=null){
                            cell.setCellValue(((BigDecimal)objs.get("refund")).doubleValue());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(13);
                        if(objs.get("busget")!=null){
                            cell.setCellValue(objs.get("busget").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(14);
                        if(objs.get("agentBusget2")!=null){
                            cell.setCellValue(objs.get("agentBusget2").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(15);
                        if(objs.get("agentget")!=null){
                            cell.setCellValue(objs.get("agentget").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(16);
                        if(objs.get("yhprice")!=null){
                            cell.setCellValue(objs.get("yhprice").toString());
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(17);
                        if(objs.get("coefficient")!=null){
                            cell.setCellValue(objs.get("coefficient").toString()+"%");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(18);
                        if(objs.get("isDeliver")!=null){
                            Integer isDeliver=(Integer)objs.get("isDeliver");
                            if(isDeliver!=null&&isDeliver==1){
                                cell.setCellValue("商家配送");
                            }else{
                                cell.setCellValue("平台配送");
                            }

                        }else{
                            cell.setCellValue("平台配送");
                        }
                        cell.setCellStyle(cellStyle);
                    }else{
                        if(type==1){
                            cell = row.createCell(8);
                            if(objs.get("showps")!=null){
                                cell.setCellValue(((BigDecimal)objs.get("showps")).doubleValue());
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(9);
                            if(objs.get("refund")!=null){
                                cell.setCellValue(((BigDecimal)objs.get("refund")).doubleValue());
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(10);
                            if(objs.get("busget")!=null){
                                cell.setCellValue(objs.get("busget").toString().toString());
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(11);
                            if(objs.get("agentget")!=null){
                                cell.setCellValue(objs.get("agentget").toString());
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(12);
                            if(objs.get("yhprice")!=null){
                                cell.setCellValue((Double)objs.get("yhprice"));
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(13);
                            if(objs.get("coefficient")!=null){
                                cell.setCellValue((Double)objs.get("coefficient")+"%");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(14);
                            if(objs.get("isDeliver")!=null){
                                Integer isDeliver=(Integer)objs.get("isDeliver");
                                if(isDeliver!=null&&isDeliver==1){
                                    cell.setCellValue("商家配送");
                                }else{
                                    cell.setCellValue("平台配送");
                                }
                            }else{
                                cell.setCellValue("平台配送");
                            }
                            cell.setCellStyle(cellStyle);
                        }else{
                            cell = row.createCell(8);
                            if(objs.get("subsidy")!=null){
                                cell.setCellValue(((BigDecimal)objs.get("subsidy")).doubleValue());
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(9);
                            if(objs.get("showps")!=null){//配送费
                                cell.setCellValue(((BigDecimal)objs.get("showps")).doubleValue());
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(10);
                            if(objs.get("refund")!=null){//退款金额
                                cell.setCellValue(((BigDecimal)objs.get("refund")).doubleValue());
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(11);
                            if(objs.get("isDeliver")!=null){
                                Integer isDeliver=(Integer)objs.get("isDeliver");
                                if(isDeliver!=null&&isDeliver==1){
                                    cell.setCellValue("商家配送");
                                }else{
                                    cell.setCellValue("平台配送");
                                }
                            }else{
                                cell.setCellValue("平台配送");
                            }
                            cell.setCellStyle(cellStyle);
                        }
                    }


                    index ++;
                }
            }

            //明细
            sheet = workbook.createSheet();
            if(sheet!=null){
                workbook.setSheetName(1, "商家销售订单明细");
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                HSSFRow row = sheet.createRow(0);
                String[] columns;
                int[] widths;
                if(type==1){
                    columns = new String[]{"代理商","商家名称","订单编号","商品数量","商品","用户名称","用户电话","订单金额","实付金额","优惠金额（商家活动)","优惠金额（优惠券）","优惠券类型","商家收入（元）","代理商抽成","平台抽成","配送费","退款金额","退款人","下单时间","支付方式","订单完成时间","配送方式"};
                    widths=new int[]{150*35,150*35,150*35,80*35,200*35,120*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*70,100*35,100*35,100*35};
                }else if(type==2){
                    columns = new String[]{"代理商","商家名称","订单编号","提现状态","商品数量","商品","用户名称","用户电话","订单金额","实付金额","优惠金额（商家活动)","优惠金额（优惠券）","优惠券类型","商家收入（元）","代理商抽成","上级代理商抽成","平台抽成","配送费","退款金额","退款人","下单时间","支付方式","订单完成时间","配送方式"};
                    widths=new int[]{150*35,150*35,150*35,120*35,80*35,200*35,120*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*70,100*35,100*35,100*35};
                }else{
                    columns = new String[]{"代理商","商家名称","订单编号","商品数量","商品","用户名称","用户电话","订单金额","金额","优惠金额（商家活动)","优惠金额（优惠券）","优惠券类型","商家收入（元）","配送费","退款金额","退款人","下单时间","订单完成时间","配送方式"};
                    widths=new int[]{150*35,150*35,150*35,80*35,200*35,120*35,100*35,80*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*35,100*70,100*35,100*35};
                }
                HSSFCell cell = null;
                for(int i=0;i<columns.length;i++){
                    sheet.setColumnWidth(i, widths[i]);
                    cell = row.createCell(i);
                    cell.setCellValue(columns[i]);
                    cell.setCellStyle(setBorder);
                }
                int index = 1;

                for(RunfastGoodsSellRecord record:recoedList){

                    row = sheet.createRow(index);



                    cell = row.createCell(0);//代理商名称
                    cell.setCellValue(record.getAgentname());
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(1);//商家名称
                    cell.setCellValue(record.getBusinessname());
                    cell.setCellStyle(cellStyle);

                    cell = row.createCell(2);//订单编号
                    cell.setCellValue(record.getOrdercode());
                    cell.setCellStyle(cellStyle);

                    if(type==2){

                        cell = row.createCell(3);//提现状态
                        if(record.getIswithdraw()!=null){
                            if(record.getIswithdraw()==1){
                                cell.setCellValue("已提现");
                            }else{
                                cell.setCellValue("未提现");
                            }
                        }else{
                            cell.setCellValue("未提现");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(4);//商品数量
                        cell.setCellValue(record.getGoodstotal());
                        cell.setCellStyle(cellStyle);

                        List<RunfastGoodsSellChildren> childrenList = record.getGoodsSellRecordChildren();
                        if(childrenList==null){RunfastGoodsSellChildrenExample example = new RunfastGoodsSellChildrenExample();
                            RunfastGoodsSellChildrenExample.Criteria criteria = example.createCriteria();
                            criteria.andOrdercodeEqualTo(record.getOrdercode());
                            childrenList = runfastGoodsSellChildrenService.selectByExample(example);
                        }

                        String goodsStr = "";
                        if(childrenList!=null && childrenList.size()>0){
                            for(RunfastGoodsSellChildren c :childrenList){
                                if(c!=null && !ComUtil.isEmpty(c.getGoodssellname()) && c.getNum()!=null){
                                    goodsStr +=c.getGoodssellname()+"*"+c.getNum()+",  ";
                                }
                            }
                        }
                        cell = row.createCell(5);//商品
                        cell.setCellValue(goodsStr);
                        cell.setCellStyle(cellStyle);
//						"用户名称","用户电话",
//						"金额","订单时间"};
                        cell = row.createCell(6);//用户名称
                        cell.setCellValue(record.getUsername());
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(7);//用户电话
                        cell.setCellValue(record.getUsermobile());
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(8);//订单金额
                        if(record.getDisprice()!=null){
                            cell.setCellValue(record.getDisprice().add(record.getShowps())+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(9);//实付金额
                        if(record.getTotalpay()!=null){
                            cell.setCellValue(record.getTotalpay()+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(10);//优惠金额（商家活动）
                        if(record.getActivityprice()!=null){
                            cell.setCellValue(record.getActivityprice()+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(11);//优惠金额（优惠券）
                        if(record.getYhprice()!=null){
                            cell.setCellValue(record.getYhprice()+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(12);//使用范围 1 全部 2指定商家3指定代理商
                        if(record.getRange1()!=null){
                            if(record.getRange1()==1){
                                cell.setCellValue("平台");
                            }else if(record.getRange1()==2){
                                cell.setCellValue("商家");
                            }else if(record.getRange1()==3){
                                cell.setCellValue("代理商");
                            }

                        }else{
                            cell.setCellValue("");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(13);//商家收入
                        if(record.getBusinesspay()!=null){
                            BigDecimal bugget=record.getBusinessget();
                            if(bugget==null){
                                bugget=BigDecimal.valueOf(0);
                            }
                            cell.setCellValue(record.getBusinesspay().subtract(bugget).setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(14);//代理商收入
                        if(record.getBusinessget()!=null){
                            double agget=record.getYhprice()==null?0:record.getYhprice();
                            /*if(agget==null){
                                agget=BigDecimal.valueOf(0);
                            }*/
                            if(record.getRange1()!=null&&record.getRange1()==3){
                                cell.setCellValue(record.getAgentbusget().subtract(BigDecimal.valueOf(agget)).setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue(record.getAgentbusget()+"");
                            }
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(15);//上级代理商收入
                        if(record.getAgentbusget2()!=null){
                            cell.setCellValue(record.getAgentbusget2()+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(16);//平台收入
                        if(record.getAgentget()!=null){
                            cell.setCellValue(record.getAgentget().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(17);//配送费
                        if(record.getShowps()!=null){
                            cell.setCellValue(record.getShowps().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(18);//退款金额
                        if(record.getRefund()!=null){
                            cell.setCellValue(record.getRefund().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(19);//退款人
                        if(record.getRefundtype()!=null){
                            if(record.getRefundtype()==1){
                                cell.setCellValue("商家");
                            }else if(record.getRefundtype()==2){
                                cell.setCellValue("代理商");
                            }else if(record.getRefundtype()==3){
                                cell.setCellValue("平台");
                            }

                        }else{
                            cell.setCellValue("");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(20);//订单时间
                        if(record.getCreatetime() != null){
                            cell.setCellValue(sdf.format(record.getCreatetime()));
                        }else{
                            cell.setCellValue("");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(21);//支付方式
                        if(record.getPaytype() != null){
                            if(record.getPaytype() ==0){
                                cell.setCellValue("支付宝");
                            }else  if(record.getPaytype() ==1){
                                cell.setCellValue("微信");
                            }else if(record.getPaytype() ==2){
                                cell.setCellValue("钱包");
                            }

                        }else{
                            cell.setCellValue("");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(22);//订单完成时间
                        if(record.getReadytime() != null){
                            cell.setCellValue(sdf.format(record.getReadytime()));
                        }else{
                            cell.setCellValue("");
                        }
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(23);//
                        if(record.getIsdeliver()!=null){
                            if(record.getIsdeliver()==1){
                                cell.setCellValue("商家配送");
                            }else{
                                cell.setCellValue("平台配送");
                            }
                        }else{
                            cell.setCellValue("平台配送");
                        }
                        cell.setCellStyle(cellStyle);

                    }else{
                        cell = row.createCell(3);//商品数量
                        cell.setCellValue(record.getGoodstotal());
                        cell.setCellStyle(cellStyle);

                        List<RunfastGoodsSellChildren> childrenList = record.getGoodsSellRecordChildren();
                        if(childrenList==null){
                            RunfastGoodsSellChildrenExample example = new RunfastGoodsSellChildrenExample();
                            RunfastGoodsSellChildrenExample.Criteria criteria = example.createCriteria();
                            criteria.andOrdercodeEqualTo(record.getOrdercode());
                            childrenList = runfastGoodsSellChildrenService.selectByExample(example);
                        }

                        String goodsStr = "";
                        if(childrenList!=null && childrenList.size()>0){
                            for(RunfastGoodsSellChildren c :childrenList){
                                if(c!=null && !ComUtil.isEmpty(c.getGoodssellname()) && c.getNum()!=null){
                                    goodsStr +=c.getGoodssellname()+"*"+c.getNum()+",  ";
                                }
                            }
                        }
                        cell = row.createCell(4);//商品
                        cell.setCellValue(goodsStr);
                        cell.setCellStyle(cellStyle);
//						"用户名称","用户电话",
//						"金额","订单时间"};
                        cell = row.createCell(5);//用户名称
                        cell.setCellValue(record.getUsername());
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(6);//用户电话
                        cell.setCellValue(record.getUsermobile());
                        cell.setCellStyle(cellStyle);

                        cell = row.createCell(7);//订单金额
                        if(record.getDisprice()!=null){
                            cell.setCellValue(record.getPrice()+"");
                        }else{
                            cell.setCellValue("0");
                        }
                        cell.setCellStyle(cellStyle);

                        if(type==1){
                            cell = row.createCell(8);//实付金额
                            if(record.getTotalpay()!=null){
                                cell.setCellValue(record.getTotalpay()+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(9);//优惠金额（商家活动）
                            if(record.getActivityprice()!=null){
                                cell.setCellValue(record.getActivityprice()+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(10);//优惠金额（优惠券）
                            if(record.getYhprice()!=null){
                                cell.setCellValue(record.getYhprice()+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(11);//使用范围 1 全部 2指定商家3指定代理商
                            if(record.getRange1()!=null){
                                if(record.getRange1()==1){
                                    cell.setCellValue("平台");
                                }else if(record.getRange1()==2){
                                    cell.setCellValue("商家");
                                }else if(record.getRange1()==3){
                                    cell.setCellValue("代理商");
                                }

                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(12);//商家收入
                            if(record.getBusinesspay()!=null){
                                BigDecimal bugget=record.getBusinessget();
                                if(bugget==null){
                                    bugget=BigDecimal.valueOf(0);
                                }
                                cell.setCellValue(record.getBusinesspay().subtract(bugget).setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(13);//代理商收入
                            if(record.getBusinessget()!=null){
                                double agget=record.getYhprice()==null?0:record.getYhprice();
                                /*if(agget==null){
                                    agget=BigDecimal.valueOf(0);
                                }*/
                                if(record.getRange1()!=null&&record.getRange1()==3){
                                    cell.setCellValue(record.getAgentbusget().subtract(BigDecimal.valueOf(agget)).setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                                }else{
                                    cell.setCellValue(record.getAgentbusget()+"");
                                }

                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(14);//平台收入
                            if(record.getAgentget()!=null){
                                cell.setCellValue(record.getAgentget().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(15);//配送费
                            if(record.getShowps()!=null){
                                cell.setCellValue(record.getShowps().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(16);//退款金额
                            if(record.getRefund()!=null){
                                cell.setCellValue(record.getRefund().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(17);//退款人
                            if(record.getRefundtype()!=null){
                                if(record.getRefundtype()==1){
                                    cell.setCellValue("商家");
                                }else if(record.getRefundtype()==2){
                                    cell.setCellValue("代理商");
                                }else if(record.getRefundtype()==3){
                                    cell.setCellValue("平台");
                                }

                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(18);//订单时间
                            if(record.getCreatetime() != null){
                                cell.setCellValue(sdf.format(record.getCreatetime()));
                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(19);//支付方式
                            if(record.getPaytype() != null){
                                if(record.getPaytype() ==0){
                                    cell.setCellValue("支付宝");
                                }else  if(record.getPaytype() ==1){
                                    cell.setCellValue("微信");
                                }else if(record.getPaytype() ==2){
                                    cell.setCellValue("钱包");
                                }

                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(20);//订单完成时间
                            if(record.getReadytime() != null){
                                cell.setCellValue(sdf.format(record.getReadytime()));
                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(21);//
                            if(record.getIsdeliver()!=null){
                                if(record.getIsdeliver()==1){
                                    cell.setCellValue("商家配送");
                                }else{
                                    cell.setCellValue("平台配送");
                                }
                            }else{
                                cell.setCellValue("平台配送");
                            }
                            cell.setCellStyle(cellStyle);
                        }else{
                            cell = row.createCell(8);//金额（商家部分）
                            if(record.getBusinesspay()!=null){
                                cell.setCellValue(record.getBusinesspay()+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(9);//优惠金额（商家活动）
                            if(record.getActivityprice()!=null){
                                cell.setCellValue(record.getActivityprice()+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(10);//优惠金额（优惠券）
                            if(record.getYhprice()!=null){
                                cell.setCellValue(record.getYhprice()+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(11);//使用范围 1 全部 2指定商家3指定代理商
                            if(record.getRange1()!=null){
                                if(record.getRange1()==1){
                                    cell.setCellValue("平台");
                                }else if(record.getRange1()==2){
                                    cell.setCellValue("商家");
                                }else if(record.getRange1()==3){
                                    cell.setCellValue("代理商");
                                }

                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);


                            cell = row.createCell(12);//商家收入
                            if(record.getBusinesspay()!=null){
                                BigDecimal bugget=record.getBusinessget();
                                if(bugget==null){
                                    bugget=BigDecimal.valueOf(0);
                                }
                                cell.setCellValue(record.getBusinesspay().subtract(bugget).setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(13);//配送费
                            if(record.getShowps()!=null){
                                cell.setCellValue(record.getShowps().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(14);//退款金额
                            if(record.getRefund()!=null){
                                cell.setCellValue(record.getRefund().setScale(2, BigDecimal.ROUND_HALF_UP)+"");
                            }else{
                                cell.setCellValue("0");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(15);//退款人
                            if(record.getRefundtype()!=null){
                                if(record.getRefundtype()==1){
                                    cell.setCellValue("商家");
                                }else if(record.getRefundtype()==2){
                                    cell.setCellValue("代理商");
                                }else if(record.getRefundtype()==3){
                                    cell.setCellValue("平台");
                                }

                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);


                            cell = row.createCell(16);//订单时间
                            if(record.getCreatetime() != null){
                                cell.setCellValue(sdf.format(record.getCreatetime()));
                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(17);//订单完成时间
                            if(record.getReadytime() != null){
                                cell.setCellValue(sdf.format(record.getReadytime()));
                            }else{
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(cellStyle);

                            cell = row.createCell(18);//
                            if(record.getIsdeliver()!=null){
                                if(record.getIsdeliver()==1){
                                    cell.setCellValue("商家配送");
                                }else{
                                    cell.setCellValue("平台配送");
                                }
                            }else{
                                cell.setCellValue("平台配送");
                            }
                            cell.setCellStyle(cellStyle);
                        }
                    }

                    index ++;
                }
            }

            /*String fileName = Long.toString(System.currentTimeMillis()) + ".xls";
            BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(fileName));
            workbook.write(bos);
            bos.flush();
            bos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();*/
        } catch (Exception e) {
            e.printStackTrace();
        }

        return workbook;

    }
}
